package com.bdd.utils;

import com.bdd.bean.BusLine;
import com.bdd.bean.BusStop;
import com.bdd.bean.Points;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DBConn {

    Connection con = null;
    Statement stm = null;
    String sql = "";

    public DBConn() {

    }

    public void creatConnectInstance() {
        try {
            Class.forName("org.postgresql.Driver").newInstance();
            String url = "jdbc:postgresql://localhost:5432/buslgis";
            con = DriverManager.getConnection(url, "postgres", "11225566");
            stm = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }

    public void destroyConnectInstance() {
        try {
            con.close();
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }

    public void close() {
        try {
            if (stm != null)
                stm.close();
            if (con != null)
                con.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public BusLine queryLineByName(String LineName) {
        BusLine busLine = new BusLine();
        sql = "select id,name,type,polyline from busline where name='" + LineName + "'";
        this.creatConnectInstance();
        ResultSet rs = null;
        try {
            rs = stm.executeQuery(sql);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        try {
            while (rs.next()) {
                String id = rs.getString("id");
                String name = rs.getString("name");
                String type = rs.getString("type");
                String polyline = rs.getString("polyline");
                List<Points> pointses = new ArrayList<>();
                String[] lines = polyline.split(";");
                for (int k = 0; k < lines.length; k++) {
                    String[] pots = lines[k].split(",");
                    String x = pots[0];
                    String y = pots[1];
                    Points busStop = new Points(Double.valueOf(x), Double.valueOf(y));
                    pointses.add(busStop);
                }
                busLine.setId(id);
                busLine.setName(name);
                busLine.setType(type);
                busLine.setPolyline(pointses);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        this.destroyConnectInstance();
        this.close();
        System.out.println("查询完成" + busLine.getName());
        return busLine;
    }

    //id和线路名才唯一确定一个点,但是出现了一个问题。数据有问题，故而只有想办法，以最后一个点坐标去做分析。
    public BusStop queryBusStopById(String BusStopId) {
        BusStop busStop = new BusStop();
        sql = "select id,line,sequence,name,locationx,locationy from busstop where id='" + BusStopId + "'";
        this.creatConnectInstance();
        ResultSet rs = null;
        try {
            rs = stm.executeQuery(sql);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        try {
            while (rs.next()) {
                String id = rs.getString("id");
                String line = rs.getString("line");
                String sequence = rs.getString("sequence");
                String name = rs.getString("name");
                String locationx = rs.getString("locationx");
                String locationy = rs.getString("locationy");
                Points points = new Points(Double.valueOf(locationx), Double.valueOf(locationy));
                busStop.setId(id);
                busStop.setName(name);
                busStop.setPoints(points);
                busStop.setLine(line);
                busStop.setSequence(sequence);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        this.destroyConnectInstance();
        this.close();
        System.out.println("查询完成" + busStop.getName());
        return busStop;
    }

    public List<BusStop> queryArrival_Min(String BusStopId, double distance, double distance2) {
        List<BusStop> busStopList = new ArrayList<>();
        sql = "select busstop.* from querybusmin('"
                + BusStopId
                + "',"
                + distance
                + ","
                + distance2
                + ") as min ,busstop  where busstop.line=min.line and busstop.sequence=min.minsequence";
        this.creatConnectInstance();
        ResultSet rs = null;
        try {
            rs = stm.executeQuery(sql);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        try {
            while (rs.next()) {
                BusStop busStop = new BusStop();
                String id = rs.getString("id");
                String line = rs.getString("line");
                String sequence = rs.getString("sequence");
                String name = rs.getString("name");
                String locationx = rs.getString("locationx");
                String locationy = rs.getString("locationy");
                Points points = new Points(Double.valueOf(locationx), Double.valueOf(locationy));
                busStop.setId(id);
                busStop.setName(name);
                busStop.setPoints(points);
                busStop.setLine(line);
                busStop.setSequence(sequence);
                busStopList.add(busStop);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        this.destroyConnectInstance();
        this.close();
        System.out.println("查询可达最小完成" + BusStopId);
        return busStopList;
    }

    public List<BusStop> queryArrival_Max(String BusStopId, double distance, double distance2) {
        List<BusStop> busStopList = new ArrayList<>();
        sql = "select busstop.* from querybusmax('"
                + BusStopId
                + "',"
                + distance
                + ","
                + distance2
                + ") as max ,busstop  where busstop.line=max.line and busstop.sequence=max.maxsequence";
        this.creatConnectInstance();
        ResultSet rs = null;
        try {
            rs = stm.executeQuery(sql);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        try {
            while (rs.next()) {
                BusStop busStop = new BusStop();
                String id = rs.getString("id");
                String line = rs.getString("line");
                String sequence = rs.getString("sequence");
                String name = rs.getString("name");
                String locationx = rs.getString("locationx");
                String locationy = rs.getString("locationy");
                Points points = new Points(Double.valueOf(locationx), Double.valueOf(locationy));
                busStop.setId(id);
                busStop.setName(name);
                busStop.setPoints(points);
                busStop.setLine(line);
                busStop.setSequence(sequence);
                busStopList.add(busStop);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        this.destroyConnectInstance();
        this.close();
        System.out.println("查询可达最大完成" + BusStopId);
        return busStopList;
    }

    public List<BusStop> queryArrival_Max(Points centerPoints, double distance, double distance2) {
        List<BusStop> busStopList = new ArrayList<>();
        sql = "select busstop.* from querybusmax('"
                + centerPoints.getX()
                + "','"
                + centerPoints.getY()
                + "',"
                + distance
                + ","
                + distance2
                + ") as max ,busstop  where busstop.line=max.line and busstop.sequence=max.maxsequence";
        this.creatConnectInstance();
        ResultSet rs = null;
        try {
            rs = stm.executeQuery(sql);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        try {
            while (rs.next()) {
                BusStop busStop = new BusStop();
                String id = rs.getString("id");
                String line = rs.getString("line");
                String sequence = rs.getString("sequence");
                String name = rs.getString("name");
                String locationx = rs.getString("locationx");
                String locationy = rs.getString("locationy");
                Points points = new Points(Double.valueOf(locationx), Double.valueOf(locationy));
                busStop.setId(id);
                busStop.setName(name);
                busStop.setPoints(points);
                busStop.setLine(line);
                busStop.setSequence(sequence);
                busStopList.add(busStop);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        this.destroyConnectInstance();
        this.close();
        System.out.println("查询可达最大完成" + centerPoints.getX() + "," + centerPoints.getY());
        return busStopList;
    }

    public List<BusStop> queryArrival_Min(Points centerPoints, double distance, double distance2) {
        List<BusStop> busStopList = new ArrayList<>();
        sql = "select busstop.* from querybusmin('"
                + centerPoints.getX()
                + "','"
                + centerPoints.getY()
                + "',"
                + distance
                + ","
                + distance2
                + ") as min ,busstop  where busstop.line=min.line and busstop.sequence=min.minsequence";
        this.creatConnectInstance();
        ResultSet rs = null;
        try {
            rs = stm.executeQuery(sql);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        try {
            while (rs.next()) {
                BusStop busStop = new BusStop();
                String id = rs.getString("id");
                String line = rs.getString("line");
                String sequence = rs.getString("sequence");
                String name = rs.getString("name");
                String locationx = rs.getString("locationx");
                String locationy = rs.getString("locationy");
                Points points = new Points(Double.valueOf(locationx), Double.valueOf(locationy));
                busStop.setId(id);
                busStop.setName(name);
                busStop.setPoints(points);
                busStop.setLine(line);
                busStop.setSequence(sequence);
                busStopList.add(busStop);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        this.destroyConnectInstance();
        this.close();
        System.out.println("查询可达最小完成" + +centerPoints.getX() + "," + centerPoints.getY());
        return busStopList;
    }

    public List<String> queryALLBusStopID() {
        List<String> strList = new ArrayList<>();
        sql = "select id from busstop ";
        this.creatConnectInstance();
        ResultSet rs = null;
        try {
            rs = stm.executeQuery(sql);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        try {
            while (rs.next()) {
                String id = rs.getString("id");
                strList.add(id);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        this.destroyConnectInstance();
        this.close();
        System.out.println("获取所有的busstopid");
        return strList;
    }


    public void updateGradeByID(double grade, String id) {
        List<String> strList = new ArrayList<>();
        sql = "update busstop set grade=" + grade + " where id='" + id + "'";
        this.creatConnectInstance();
        try {
            int code = stm.executeUpdate(sql);
            if (code != -1) {
                System.out.println("更新grade" + id);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        this.destroyConnectInstance();
        this.close();
    }
}
